package com.hbue.springboot.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.hbue.springboot.pojo.DTO.TbHistoryDTO;
import com.hbue.springboot.pojo.TbHistory;
import com.hbue.springboot.vo.WorkerSalaryVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import javax.xml.crypto.Data;
import java.sql.Date;
import java.util.List;

@Repository
public interface TbHistoryMapper extends BaseMapper<TbHistory> {

    /**
     * 送水工的单子
     */
    @Select("select * from tb_history where worker_id = #{id}")
    public List<TbHistory> selectByWorkerId(int id);

    @Select("SELECT\n" +
            "\th.id,\n" +
            "\tw.worker_name,\n" +
            "\tc.cust_name,\n" +
            "\th.send_water_time,\n" +
            "\th.send_water_count \n" +
            "FROM\n" +
            "\t`tb_history` h\n" +
            "\tLEFT JOIN tb_worker w ON h.worker_id = w.id\n" +
            "\tLEFT JOIN tb_customer c ON h.cust_id = c.id\n" +
            "\tLEFT JOIN tb_account a ON a.user_name = c.cust_name \n" +
            "${ew.customSqlSegment} and c.cust_name = a.user_name")
    Page<TbHistoryDTO> listByPageBetween(Page<TbHistoryDTO> page, @Param(Constants.WRAPPER) Wrapper<TbHistory> wrapper);

    @Select("SELECT\n" +
            "\th.id,\n" +
            "\tw.worker_name,\n" +
            "\tc.cust_name,\n" +
            "\th.send_water_time,\n" +
            "\th.send_water_count \n" +
            "FROM\n" +
            "\t`tb_history` h\n" +
            "\tLEFT JOIN tb_customer c ON h.cust_id = c.id\n" +
            "\tLEFT JOIN tb_worker w ON h.worker_id = w.id\n" +
            "\tLEFT JOIN tb_account a ON a.user_name = w.worker_name \n" +
            "${ew.customSqlSegment} and w.worker_name = #{userName}")
    Page<TbHistoryDTO> listForWorkerByPageBetween(Page<TbHistoryDTO> page, @Param(Constants.WRAPPER) Wrapper<TbHistory> wrapper,
                                                  @Param("userName") String userName);

    @Select("SELECT\n" +
            "\th.id,\n" +
            "\tw.worker_name,\n" +
            "\tc.cust_name,\n" +
            "\th.send_water_time,\n" +
            "\th.send_water_count \n" +
            "FROM\n" +
            "\t`tb_history` h\n" +
            "\tLEFT JOIN tb_customer c ON h.cust_id = c.id\n" +
            "\tLEFT JOIN tb_worker w ON h.worker_id = w.id\n" +
            "\tLEFT JOIN tb_account a ON c.cust_name = a.user_name \n" +
            "${ew.customSqlSegment} and c.cust_name = #{userName}")
    Page<TbHistoryDTO> listForCustByPageBetween(Page<TbHistoryDTO> page, @Param(Constants.WRAPPER) Wrapper<TbHistory> wrapper,
                                                @Param("userName") String userName);

    @Select("SELECT\n" +
            "\th.id,\n" +
            "\tw.worker_name,\n" +
            "\tc.cust_name,\n" +
            "\th.send_water_time,\n" +
            "\th.send_water_count \n" +
            "FROM\n" +
            "\t`tb_history` h\n" +
            "\tLEFT JOIN tb_worker w ON h.worker_id = w.id\n" +
            "\tLEFT JOIN tb_customer c ON h.cust_id = c.id\n" +
            "\tLEFT JOIN tb_account a ON a.user_name = c.cust_name \n" +
            "WHERE\n" +
            "\tc.cust_name = a.user_name")
    Page<TbHistoryDTO> listByPage(Page<TbHistoryDTO> page);

    @Select("SELECT\n" +
            "\th.id,\n" +
            "\tw.worker_name,\n" +
            "\tc.cust_name,\n" +
            "\th.send_water_time,\n" +
            "\th.send_water_count \n" +
            "FROM\n" +
            "\t`tb_history` h\n" +
            "\tLEFT JOIN tb_customer c ON h.cust_id = c.id\n" +
            "\tLEFT JOIN tb_worker w ON h.worker_id = w.id\n" +
            "\tLEFT JOIN tb_account a ON a.user_name = w.worker_name \n" +
            "WHERE\n" +
            "\tw.worker_name = #{userName}")
    Page<TbHistoryDTO> listForWorkerByPage(Page<TbHistoryDTO> page, @Param("userName") String userName);

    @Select("SELECT\n" +
            "\th.id,\n" +
            "\tw.worker_name,\n" +
            "\tc.cust_name,\n" +
            "\th.send_water_time,\n" +
            "\th.send_water_count \n" +
            "FROM\n" +
            "\t`tb_history` h\n" +
            "\tLEFT JOIN tb_worker w ON h.worker_id = w.id\n" +
            "\tLEFT JOIN tb_customer c ON h.cust_id = c.id\n" +
            "\tLEFT JOIN tb_account a ON a.user_name = c.cust_name \n" +
            "WHERE\n" +
            "\tc.cust_name = #{userName}")
    Page<TbHistoryDTO> listForCustByPage(Page<TbHistoryDTO> page, @Param("userName") String userName);

    @Select("SELECT w.id, w.worker_name , w.worker_salary, w.worker_money,\n" +
            "ifnull(sum(h.send_water_count),0) sendWaterCount,\n" +
            "ifnull(sum(h.send_water_count * w.worker_money)+ w.worker_salary ,w.worker_salary ) as finalSalary\n" +
            "FROM tb_worker w left join tb_history h on w.id = h.worker_id\n" +
            "${ew.customSqlSegment}\n" +
            "GROUP BY w.id\n" +
            "order by  finalSalary desc")
    Page<WorkerSalaryVo> listSalaryByPageBetween(Page<WorkerSalaryVo> page, @Param(Constants.WRAPPER) QueryWrapper<TbHistory> queryWrapper);

    @Select("SELECT w.id, w.worker_name , w.worker_salary, w.worker_money,\n" +
            "-- 计算送水数量\n" +
            "ifnull(sum(h.send_water_count),0) sendWaterCount,\n" +
            "-- 计算工资  公式：实发工资（  送水数量 * 送水提成 +底薪）\n" +
            "ifnull(sum(h.send_water_count * w.worker_money)+ w.worker_salary ,w.worker_salary ) as finalSalary\n" +
            "FROM tb_worker w left join tb_history h on w.id = h.worker_id\n" +
            "where h.send_water_time BETWEEN '2025-01-01' and '2025-01-31'\n" +
            "GROUP BY w.id\n" +
            "order by  finalSalary desc;")
    Page<WorkerSalaryVo> listSalaryByPage(Page<WorkerSalaryVo> page);

    @Select("select * from tb_history where cust_id = #{id} and send_water_time is null order by id desc")
    IPage<TbHistory> getOrderWithoutFinish(Page<TbHistory> page, @Param("id")Integer id);
}
